import pymysql


class DatabaseManager:
    def __init__(self, host,port, user, password, db_name):
        """
        初始化数据库连接参数。

        参数:
        - host: 数据库服务器地址
        - port: 数据库端口
        - user: 用户名
        - password: 密码
        - db_name: 数据库名称
        """
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db_name = db_name
        self.connection = None

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(host=self.host,
                                              port=self.port,
                                            user=self.user,
                                            password=self.password,
                                            database=self.db_name,
                                            charset='utf8mb4',
                                            cursorclass=pymysql.cursors.DictCursor)
            print("成功连接到数据库")
        except Exception as e:
            print(f"连接数据库时出错: {e}")
            self.connection=None

    def read_data(self, query):
        """
        执行查询并返回结果。

        参数:
        - query: SQL查询语句
        返回:
        - 查询结果（列表形式），每个元素为字典
        """
        if self.connection is None:
            print("数据库未连接")
            return None

        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchall()
                return result
        except Exception as e:
            print(f"执行查询时出错: {e}")
            return None

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            print("数据库连接已关闭")
        else:
            print("没有活跃的数据库连接")


# 示例：如何使用DatabaseManager类
if __name__ == "__main__":
    # 假设你的数据库配置如下
    db_config={
        'host': '192.168.10.5',
        'port':3307,
        'user': 'root',
        'password': '123456',
        'db_name': 'saas_back'
    }

    # 创建DatabaseManager实例
    db_manager = DatabaseManager(**db_config)

    # 连接到数据库
    db_manager.connect()

    # 执行查询
    # query = "SELECT file_name FROM sys_files;"
    query = "SELECT user_name,role_name,file_name from sys_users u LEFT JOIN sys_user_roles ur ON u.ID = ur.user_id LEFT JOIN sys_roles r ON r.role_id = ur.role_id LEFT JOIN sys_files f ON  u.ID = f.ID WHERE u.user_name= '童月伟管理员'"

    data = db_manager.read_data(query)
    if data:
        for row in data:
            print(row)

    # 关闭数据库连接
    db_manager.close()